
package com.cl.ks.service;

import com.cl.ks.entity.SqlTemplate;
import com.cl.ks.repository.SqlTemplateRepository;
import com.cl.mvc.service.BaseService;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import com.alibaba.fastjson.JSONObject;
import com.cl.ks.utils.DbUtil;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.util.*;

import static com.cl.ks.constant.QTableConstant.qSqlTemplate;

import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Slf4j
@Service
public class SqlTemplateServiceUtil extends BaseService<SqlTemplate> {
    @Autowired
    JPAQueryFactory queryFactory;
    @Autowired
    DbUtil dbUtil;

    private static final Map<String, String> AREA_MAP;
    private static final Map<String, List<String>> AREA_MAPPING;

    static {
        AREA_MAP = new HashMap<>();
        AREA_MAP.put("all", "all");
        AREA_MAP.put("hangzhou", "杭州市");
        AREA_MAP.put("yuhang", "余杭区");
        AREA_MAP.put("xiaoshan", "萧山区");
        AREA_MAP.put("linan", "临安区");
        AREA_MAP.put("jiande", "建德市");
        AREA_MAP.put("chunan", "淳安县");
        AREA_MAP.put("fuyang", "富阳区");
        AREA_MAP.put("tonglu", "桐庐县");
    }

    static {
        AREA_MAPPING = new HashMap<>();

        // 钱塘区、萧山区统一属于萧山区
        AREA_MAPPING.put("萧山区", Arrays.asList("萧山区", "钱塘区"));

        // 西湖区、拱墅区、上城区、下城区、江干区、滨江区统一属于杭州市
        AREA_MAPPING.put("杭州市", Arrays.asList("西湖区", "拱墅区", "上城区", "下城区", "江干区", "滨江区"));

        // 余杭区、临平区统一属于余杭区
        AREA_MAPPING.put("余杭区", Arrays.asList("余杭区", "临平区"));
    }

    private static final Map<String, JSONObject> resCache = new ConcurrentHashMap<>();

    protected SqlTemplateServiceUtil(SqlTemplateRepository SqlTemplateRepository) {
        super(SqlTemplateRepository);
    }

    @Async("taskExecutor")
    @Scheduled(cron = "0 0 2 * * ?")  // 每天凌晨2点定时执行
    public void init() {
        // 为每个区域启动一个异步任务，避免主任务执行时间过长
        AREA_MAP.keySet().forEach(this::listAll);
    }

    @Async("taskExecutor")
    public void listAll(String areaCode) {
        try {
            String areaName = AREA_MAP.getOrDefault(areaCode, areaCode);
            String tempTableName = "fl_clue_temp_" + areaCode;  // 为每个区域生成唯一的临时表名

            cleanUp(areaCode);
            String createTempTableSql = generateCreateTempTableSql(areaName, tempTableName);
            dbUtil.executeSQL(null, createTempTableSql);

            JSONObject res = new JSONObject();
            List<SqlTemplate> sqlList = new ArrayList<>();
            if ("all".equals(areaCode)) {
                sqlList = queryFactory.selectFrom(qSqlTemplate).where(qSqlTemplate.category.isNotNull()).fetch();
            } else {
                sqlList = queryFactory.selectFrom(qSqlTemplate).where(qSqlTemplate.category.isNotNull().and(qSqlTemplate.tableNamePlaceHolder.isNotNull())).fetch();

            }

            // 执行SQL模板并填充结果到缓存
            for (SqlTemplate sqlTemplate : sqlList) {
                processSqlTemplate(res, sqlTemplate, tempTableName);
            }

            // 将结果放入缓存
            resCache.put(areaCode, res);
        } catch (Exception e) {
            log.error("Error processing area: {}", areaCode, e);
        } finally {
            cleanUp(areaCode);
        }
    }

    // 生成动态的临时表SQL
    private String generateCreateTempTableSql(String areaName, String tempTableName) {
        if ("all".equals(areaName)){
            return "CREATE TABLE " + tempTableName + " AS SELECT * FROM fl_clue;";
        }
        // 根据区域名称获取对应的多个区域
        List<String> areas = AREA_MAPPING.getOrDefault(areaName, Collections.singletonList(areaName));

        // 创建 SQL 查询
        String areaCondition = areas.stream()
                .map(area -> "JSON_UNQUOTE(JSON_EXTRACT(content, '$.businessadc_mc')) LIKE '%" + area.substring(0, 2) + "%'")
                .collect(Collectors.joining(" OR "));  // 拼接多个区域的查询条件

        // 创建临时表
        return "CREATE TABLE " + tempTableName + " AS SELECT * FROM fl_clue WHERE " + areaCondition + ";";
    }

    // 执行SQL模板并填充到结果中
    private void processSqlTemplate(JSONObject res, SqlTemplate sqlTemplate, String tableName) {
        try {
            String sqlTemplateContent = sqlTemplate.getTemplateContent();
            if (sqlTemplate.getTableNamePlaceHolder() != null) {
                sqlTemplateContent = sqlTemplateContent.replace(sqlTemplate.getTableNamePlaceHolder(), tableName);
            }
            List<Map<String, Object>> list = dbUtil.fetchSQL(null, sqlTemplateContent);
            if (sqlTemplate.getCategory() != null) {
                res.putIfAbsent(sqlTemplate.getCategory(), new JSONObject());
                if (sqlTemplate.getName() != null) {
                    res.getJSONObject(sqlTemplate.getCategory()).put(sqlTemplate.getName(), list);
                } else {
                    res.put(sqlTemplate.getCategory(), list.size() == 1 ? list.get(0) : list);
                }
            }
        } catch (Exception e) {
            log.error("Error processing SQL Template: {}", sqlTemplate.getName(), e);
        }
    }

    // 清理临时表
    private void cleanUp(String areaCode) {
        String tempTableName = "fl_clue_temp_" + areaCode;
        String dropTempTableSql = "DROP TABLE IF EXISTS " + tempTableName;
        dbUtil.executeSQL(null, dropTempTableSql);
    }

    // 获取缓存中的结果
    public JSONObject getResCache(String areaCode) {
        return resCache.getOrDefault(areaCode, resCache.get("all"));
    }
}
